import os
from typing import Sequence, Set
import xlwings as xw
import pandas as pd
from xlwings.main import Books
app = xw.App(visible=False, add_book=False)
发货人集 = dict()


class eachfile():
    # 1.创建或打开汇总文件
    # 2.子过程-遍历文件
    # 1.
    # n.关闭汇总文件
    def __init__(self):
        self.app = xw.App(visible=False, add_book=False)
        global 发货人集
        发货人集['发货人'] = dict()
        发货人集['成交计量单位'] = dict()
        发货人集['币制'] = dict()
        发货人集['境内目的地/境内货源地'] = dict()
        发货人集['商品编码'] = dict()
        发货人集['目的国'] = dict()
        self.参数集 = dict()

    def READ_TABLE(self, sht, XY=['F3', 'D3', 'D4', 'J2', 'B3', 'D8']):
        '''
        默认return\n
        0 F3发货人名称\n
        1 D3发货人代码\n
        2 D4收货人名称\n
        3 J2合同号\n
        4 B3申报日期\n
        5 D8目的国
        '''
        CELL = [sht.range(i).value for i in XY]
        return CELL

    def argset(self, XY=['发货人名称', '发货人代码', '收货人名称', '合同号', '出口日期', '目的国']):
        '''

        XY[0]=发货人名称\n
        XY[1]=发货人代码\n
        XY[2]=收货人名称
        '''
        # global ITEMS
        公司 = ('%s' % XY[0], '%s' % XY[1])
        # if not(发货人集.get(aab)):
        #     发货人集[aab]=dict()
        发货人集['目的国'][XY[5]] = 发货人集['目的国'][XY[5]] + \
            1 if XY[5] in 发货人集['目的国'] else 1
        if not(公司 in 发货人集['发货人']):
            发货人集['发货人'][公司] = dict()
            发货人集['发货人'][公司][XY[2]] = dict()
            发货人集['发货人'][公司][XY[2]]['计次器'] = 1
            发货人集['发货人'][公司][XY[2]][XY[3]] = {'%s' % XY[4], }
            # 发货人集['发货人'][公司][XY[2]][XY[3]].add(XY[4])

        elif XY[2] in 发货人集['发货人'][公司]:
            发货人集['发货人'][公司][XY[2]]['计次器'] += 1
            发货人集['发货人'][公司][XY[2]][XY[3]].add(XY[4])

        return 发货人集

    def items_test(self, sht, XY=['成交计量单位', '币制', '境内目的地/境内货源地', ['商品编号', '法定第一计量单位', '法定第二计量单位']]):
        # wb = self.app.books.open(BOOK)
        # sht = wb.sheets[SHEET]
        values = sht.range('A22').options(
            pd.DataFrame, header=1, index=False, expand='table').value
        for i in XY:
            item = values[i]
            yield item.drop_duplicates()

    def Test3(self, rootDir=r'.\工作区文件夹', level=1):
        if level == 1:
            print(rootDir)
        for lists in os.listdir(rootDir):

            path = os.path.join(rootDir, lists)
            print('│  '*(level-1)+'│--'+lists)
            # self.遍历文件(PATH=path)
            if os.path.isdir(path):
                self.Test3(path, level+1)
            # self.遍历文件(PATH=path)

    def findAllFile(self, base='.\工作区文件夹'):
        for root, ds, fs in os.walk(base):
            for f in fs:
                if f.split(".")[1] in ['xls', 'xlsx'] and not(f.startswith('~$')):
                    fullname = os.path.join(root, f)
                    print(fullname)
                    yield fullname

    def 商品项(self):
        df = None
        for file in self.findAllFile():

            _df = pd.read_excel(io=open(file, 'rb'), sheet_name='基本信息&商品信息', header=21)[
                ['商品编号', '规格型号']]
            _df['商品编号']=_df['商品编号'].astype(str)
            if df is None:
                df = _df
            else:
                df = pd.concat([df, _df], ignore_index=True).drop_duplicates(subset='商品编号',
                    keep='last').sort_values('商品编号')

        print(df)
        wb = self.app.books.add()
        sht = wb.sheets.add()
        sht.range('a1').expand('table').api.NumberFormat = "@"
        sht.range('a1').options(index=False).value=df
        wb.save(r'.\argfile.xlsx')
        wb.close()

    def 遍历文件(self, PATH='样版.xlsx', SHEET='基本信息&商品信息'):
        wb = self.app.books.open(PATH)
        sht = wb.sheets[SHEET]
        for i in self.items_test(sht=sht):
            if isinstance(i, pd.DataFrame):
                for i1, i2, i3 in i.values:
                    发货人集['商品编码'][i1] = (i2, i3)
            else:
                for i0 in i.values:
                    发货人集[i.name][i0] = 发货人集[i.name][i0] + \
                        1 if 发货人集[i.name].get(i0) else 1

        self.argset(aaa.READ_TABLE(sht=sht))


if __name__ == '__main__':
    aaa = eachfile()
    # print(aaa.argset(aaa.READ_TABLE()))

    aaa.商品项()

    # for i in aaa.items_test():
    #     if isinstance(i, pd.DataFrame):
    #         for i1, i2, i3 in i.values:
    #             发货人集['商品编码'][i1] = (i2, i3)
    #     else:
    #         for i0 in i.values:
    #             发货人集[i.name][i0] = 发货人集[i.name][i0] + \
    #                 1 if 发货人集[i.name].get(i0) else 1

    # aaa.argset(aaa.READ_TABLE())

    # print(发货人集)
